一、課程程式專案與資料庫範本SQL Github

https://github.com/ted59438/EnglishVocabulary_MySQL

• EnglishVocabularyLearning.exe：英文單字測驗最終的實作結果
• DBPlayground：課堂的C#範例專案
• EnglishVocabularyLearning_MySQL：C#練習專案
• EnglishVocabulary.sql：MySQL的範本資料
• EnglishVocabulary_MSSQL.sql：SQL Server 的範本資料

二、課堂內容

(一) 實作內容

新增一組學生的帳號密碼，密碼以非明文的方式存到資料庫

(二) 實作程式碼

private void insertStudentBtn_Click(object sender, EventArgs e)
{
    string sql;
    Dictionary<string, object> parameters;

    sql = @"SELECT COUNT(StudentID)
            FROM Student
            WHERE Username = @Username";
    parameters = new Dictionary<string, object>()
    {
        {"Username", studentUsernameTextBox.Text }
    };

    try
    {
        if (queryScalar(sql, parameters) != 0)
        {
            MessageBox.Show("學生帳號已經存在！", "錯誤", MessageBoxButtons.OK, MessageBoxIcon.Error);
            return;
        }
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message, "錯誤", MessageBoxButtons.OK, MessageBoxIcon.Error);
        return;
    }


    sql = @"INSERT
            INTO Student(StudentID, RealName, Birthdate, Username, Password)
            VALUES (@StudentID, @RealName, @Birthdate, @Username, SHA2(@Password, 256))";
    parameters = new Dictionary<string, object>()
    {
        {"StudentID", Guid.NewGuid().ToString() },
        {"RealName", studentNameTextBox.Text },
        {"Birthdate", studentBirthdatePicker.Value },
        {"Username", studentUsernameTextBox.Text },
        {"Password", studentPasswordTextBox.Text }
    };

    try
    {
        queryNoneReturn(sql, parameters);
        queryStudentBtn.PerformClick();
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message, "錯誤", MessageBoxButtons.OK, MessageBoxIcon.Error);
    }
}

/// <summary>
/// 交易查詢：新修刪
/// </summary>
/// <param name="sql"></param>
/// <param name="parameters"></param>
private void queryNoneReturn(string sql, Dictionary<string, object> parameters)
{
    MySqlCommand command = getAndBindCommand(sql, parameters);

    try
    {
        // 1. 開啟連線
        command.Connection.Open();

        // 2. 開啟Transcation
        command.Transaction = command.Connection.BeginTransaction();

        // 3. 執行查詢
        command.ExecuteNonQuery();

        // 4.1 執行成功，送出Commit，確認執行查詢後結果
        command.Transaction.Commit();

        command.Connection.Close();
    }
    catch (Exception)
    {
        // 4.2 執行失敗，送出Rollback，返回執行查詢前的結果
        command.Transaction.Rollback();
        command.Connection.Close();
        throw;
    }
}

(三) 雜湊 != 加密

雜湊定義：

經過雜湊演算法（Hashing algorithms）計算出來的資料指紋（data fingerprint），用來識別檔案與資料是否有被竄改，以保證檔案與資料確實是由原創者所提供。

加密定義：

將可輕易辨識的明文資訊，修改成不可讀的密文資訊。只有擁有解密方法的對象，經由解密過程，才能將密文還原為正常可讀的內容。

正常來說無法查詢密碼，只能直接修改密碼

• 加密需要密鑰，並可以透過解密得到原文。（可逆）
• 雜湊不需密鑰，無法逆向解出原始輸入。（不可逆，除了使用彩虹表）

所以如果看到一個網站能讓你查詢密碼，代表哪一天網站的資料庫被踢的時候
你的密碼也會被別人看光光。

(四) 雜湊函式

常見的雜湊函式：

• MD5
• SHA

1. SQL Server：
   https://docs.microsoft.com/en-us/sql/t-sql/functions/hashbytes-transact-sql?redirectedfrom=MSDN&view=sql-server-2017

2. MySQL：
   https://dev.mysql.com/doc/refman/5.6/en/encryption-functions.html
   https://mariadb.com/kb/en/library/encryption-hashing-and-compression-functions/

延伸閱讀：
http://www.tsnien.idv.tw/Security_WebBook/%E7%AC%AC%E5%9B%9B%E7%AB%A0%20%E9%9B%9C%E6%B9%8A%E8%88%87%E4%BA%82%E6%95%B8%E6%BC%94%E7%AE%97%E6%B3%95.html

(五) Transaction交易

1. 在資料庫上，每一個執行單元工作代表一個資料庫交易，每個交易包括多個SQL指令 (處理一件事情的單位)
2. 交易結束時的動作有兩個，確保該交易中的所有操作都成功完成且其結果被永久儲存在資料庫中：

• 數個步驟全部執行成功，交易才算成功並提交變更　=> Commit
• 只要當中有一個失敗，則整個交易宣告失敗並回復所有變更 => Rollback

延伸閱讀：
https://openhome.cc/Gossip/EJB3Gossip/TransactionABC.html

/// <summary>
/// 交易查詢：新修刪
/// </summary>
/// <param name="sql"></param>
/// <param name="parameters"></param>
private void queryNoneReturn(string sql, Dictionary<string, object> parameters)
{
    MySqlCommand command = getAndBindCommand(sql, parameters);

    try
    {
        // 1. 開啟連線
        command.Connection.Open();

        // 2. 開啟Transcation
        command.Transaction = command.Connection.BeginTransaction();

        // 3. 執行查詢
        command.ExecuteNonQuery();

        // 4.1 執行成功，送出Commit，確認執行查詢後結果
        command.Transaction.Commit();

        command.Connection.Close();
    }
    catch (Exception)
    {
        // 4.2 執行失敗，送出Rollback，返回執行查詢前的結果
        command.Transaction.Rollback();
        command.Connection.Close();
        throw;
    }
}